//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
//     Copyright (c) Telligent Systems Corporation.  All rights reserved.
// </copyright> 
//------------------------------------------------------------------------------

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
using CommunityServer.Configuration;

namespace CommunityServer.Components.SearchBarrel
{
    internal class SearchBarrelCriteria
    {
        public SearchQuery Query;
        public SearchTerms Terms;
    }

	/// <summary>
	/// Summary description for SearchBarrelProvider.
	/// </summary>
	public class SearchBarrelProvider : SearchProvider
	{
		string connectionString = string.Empty;
		string databaseOwner = string.Empty;

		public SearchBarrelProvider()
		{
			connectionString = ConfigurationSettings.AppSettings["SiteSqlServer"];
			databaseOwner = ConfigurationSettings.AppSettings["SiteSqlServerOwner"];
		}

        static Hashtable items = new Hashtable();

        public override SearchResultSet Search(SearchQuery query)
        {
            SearchBarrelCriteria barrelCriteria = new SearchBarrelCriteria();
            barrelCriteria.Query = query;
            barrelCriteria.Terms = SearchTerms.GetSearchTerms(query.QueryText);
            return GetSearchResults(barrelCriteria);
        }

        static SearchBarrelProvider()
        {
            XmlNode node = CSConfiguration.GetConfig().GetConfigSection("CommunityServer/SearchBarrel");
            foreach(XmlNode n in node.ChildNodes)
            {
                if(n.NodeType != XmlNodeType.Comment)
                {
                    if(n.Name == "add")
                    {
                        string itemType = n.Attributes["type"].Value;
                        Type t = Type.GetType(itemType);
                        if(t == null)
                            throw new Exception(itemType);

                        
                        BarrelItem bi = Activator.CreateInstance(Type.GetType(itemType)) as BarrelItem;
                        if(bi != null)
                        items[bi.ApplicationType] = bi;
                    }

                }
            }
        }

        private SearchResultSet GetSearchResults(SearchBarrelCriteria barrelCriteria) 
        {
            
            CSContext context = CSContext.Current;
            
            SearchResultSet result = new SearchResultSet();
			if ((barrelCriteria.Terms.And == null || barrelCriteria.Terms.And.Length == 0) &&
				(barrelCriteria.Terms.Or == null || barrelCriteria.Terms.Or.Length == 0) && 
				(barrelCriteria.Query.UserNamesToSearch == null || barrelCriteria.Query.UserNamesToSearch.Length == 0) && 
				(barrelCriteria.Query.UsersToSearch == null || barrelCriteria.Query.UsersToSearch.Length == 0) && 
				(barrelCriteria.Query.Tags == null || barrelCriteria.Query.Tags.Length == 0))
			{
				// there is no search criteria, return an empty SearchResult
				return result;
			}

			string searchSQL = SearchText(barrelCriteria,context.SettingsID);//,out searchSQL,out recordCount);

            DateTime searchDuration;
            using( SqlConnection connection = new SqlConnection(connectionString) ) 
            {
                SqlCommand command = new SqlCommand(databaseOwner + ".cs_SearchBarrel_Search", connection);
                SqlDataReader reader;

                // Mark as stored procedure
                command.CommandType = CommandType.StoredProcedure;

                // Add parameters
                command.Parameters.Add("@SearchSQL", SqlDbType.NText).Value = searchSQL;
                //command.Parameters.Add("@RecordCountSQL", SqlDbType.NVarChar, 4000).Value = recordCount;
                command.Parameters.Add("@PageIndex", SqlDbType.Int).Value = barrelCriteria.Query.PageIndex;
                command.Parameters.Add("@PageSize", SqlDbType.Int).Value = barrelCriteria.Query.PageSize;
                command.Parameters.Add("@SettingsID", SqlDbType.Int).Value = context.SettingsID;

                connection.Open();
                using(reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Process first record set 
                    //
                    while (reader.Read()) 
                    {
                        IndexPost post = new IndexPost();
                        post.Name = reader["PostName"] as string;
                        post.GroupID = (int)reader["GroupID"];
                        post.ApplicationKey = reader["ApplicationKey"] as string;
                        post.ApplicationType = (ApplicationType) (Int16)reader["ApplicationType"];
                        post.Body = reader["FormattedBody"] as string;
                        post.PostDate = (DateTime)reader["PostDate"];
                        post.SectionID = (int)reader["SectionID"];
                        post.SettingsID = (int)reader["SettingsID"];
                        post.ThreadID = (int)reader["ThreadID"];
                        post.Title = reader["Subject"] as string;
                        post.UserName = reader["PostAuthor"] as string;
                        post.PostType = (PostContentType) (int)reader["PostType"];
                        post.PostID = (int)reader["PostID"];
                        post.SectionName = reader["SectionName"] as string;

                        BarrelItem bi = items[post.ApplicationType] as BarrelItem;
                        if(bi != null)
                            bi.Format(post);

                        
                        result.Posts.Add(post);

                    }

                    // Move to the next result
                    //
                    if (reader.NextResult()) 
                    {
                        reader.Read();
                        result.TotalRecords = Convert.ToInt32(reader[0]);
                    }

                    // Get the duration of the search?
                    //
                    if (reader.NextResult()) 
                    {
                        reader.Read();

                        searchDuration = (DateTime) reader["Duration"];

                        // Calculate the number of seconds it took the search to execute
                        //
                        int ms = Convert.ToInt32(searchDuration.ToString("ff"));
                        result.SearchDuration = (double) ms / 1000;
                    }
                    reader.Close();
                }

                connection.Close();

                return result;
            }
        }

        #region SearchText

        /// <summary>
        /// Builds the baseline query for searching against the searchbarrel table.
        /// </summary>
        private string SearchText(SearchBarrelCriteria bar, int settingsID)//, out string searchSQL, out string recordCount)
        {
            bool hasORterms = false;
            string searchSQL = "SELECT DISTINCT P.PostID, P.SectionID, Weight = ({0}), P.PostDate FROM {1} cs_Posts P, cs_Sections F  WHERE {2} {3} {4} AND F.SectionID = P.SectionID  AND P.SettingsID = {5}  {7} AND F.IsSearchable = 1 AND P.IsApproved = 1 {6} {8} ORDER BY {9}";
            //recordCount = "SELECT TotalRecords = COUNT(DISTINCT B0.PostID) FROM {1}, cs_Posts P {6} WHERE {2} {3} AND {4} AND P.SettingsID = {5} {7}";
            string orSQL = "(";
            string[] clauses = new string[10];

            // OR clause in search terms
            //
            if ((null != bar.Terms.Or) && (bar.Terms.Or.Length > 0) )
            {
                hasORterms = true;

                for (int i = 0; i < bar.Terms.Or.Length; i++) 
                {
                    string barrel = "B0.WordHash = {0}";

                    orSQL += string.Format(barrel, bar.Terms.Or[i]);

                    if ((i+1) < bar.Terms.Or.Length) 
                    {
                        orSQL += " OR ";
                    } 
                    else 
                    {
                        clauses[2] += orSQL += ") AND ";
                    }

                }
            }

            // AND clause in search terms
            //
            if ((null != bar.Terms.And) && (bar.Terms.And.Length > 0) ) {

                for (int i = 0; i < bar.Terms.And.Length; i++) 
                {
                    string barrel = "B{0}";

                    // Build the clauses
                    if (hasORterms)
                        clauses[2] += string.Format(barrel, (i+1)) + ".WordHash = " + bar.Terms.And[i];
                    else
                        clauses[2] += string.Format(barrel, i) + ".WordHash = " + bar.Terms.And[i];

                    if ((i+1) < bar.Terms.And.Length) 
                    {
                        clauses[2] += " AND ";
                    }

                }

                clauses[2] += " AND ";
            }

            StringBuilder sb = new StringBuilder();
			bool hasSectionsFilter = false;
                        
			if(bar.Query.ApplicationTypes != null && bar.Query.ApplicationTypes.Length > 0)
			{
				ArrayList list = new ArrayList();
				foreach(ApplicationType at in bar.Query.ApplicationTypes)
				{
					CSApplicationData iApp = ApplicationSet.Applications[at];
					if ((iApp != null) && (iApp.Enabled))
						list.AddRange(iApp.Sections());
				}

				if (list.Count > 0)
				{
					if (hasSectionsFilter)
						sb.Append(" OR ");
					else
					{
						hasSectionsFilter = true;
						sb.Append(" AND (");
					}

					sb.Append("P.SectionID in (");

					for (int i = 0; i < list.Count; i++)
					{
						sb.Append(((Section) list[i]).SectionID);

						if ((i+1) < list.Count) 
							sb.Append(",");
					}

					sb.Append(")");
				}
			}

			if (bar.Query.GroupsToSearch != null && bar.Query.GroupsToSearch.Length > 0) 
			{
				if (hasSectionsFilter)
					sb.Append(" OR ");
				else
				{
					hasSectionsFilter = true;
					sb.Append(" AND (");
				}

				sb.Append("(F.GroupID IN (");
				for (int i = 0; i < bar.Query.GroupsToSearch.Length; i++) 
				{
					sb.Append(bar.Query.GroupsToSearch[i]);
					if ((i+1) < bar.Query.GroupsToSearch.Length) 
					{
						sb.Append(",");
					} 
				}
				sb.Append(") ");

				ArrayList list = new ArrayList();
				foreach(BarrelItem item in items.Values)
				{
					CSApplicationData iApp = ApplicationSet.Applications[item.ApplicationType];

					if ((iApp != null) && (iApp.Enabled))
						list.AddRange(iApp.Sections());
				}

				sb.Append(" AND P.SectionID IN (");
				for(int i =0; i < list.Count; i++)
				{
					sb.Append(((Section) list[i]).SectionID.ToString());
					if ((i+1) < list.Count) 
					{
						sb.Append(",");
					} 
				}
				sb.Append("))");
			}

            // FORUMS
            //
            if (bar.Query.SectionsToSearch != null && bar.Query.SectionsToSearch.Length > 0) 
            {
				if (hasSectionsFilter)
					sb.Append(" OR ");
				else
				{
					hasSectionsFilter = true;
					sb.Append(" AND (");
				}

				sb.Append("(P.SectionID IN (");
                for (int i = 0; i < bar.Query.SectionsToSearch.Length; i++) 
                {
                    sb.Append(bar.Query.SectionsToSearch[i]);
                    if ((i+1) < bar.Query.SectionsToSearch.Length) 
                    {
                        sb.Append(",");
                    } 
                }
                sb.Append(") ");

				ArrayList list = new ArrayList();
				foreach(BarrelItem item in items.Values)
				{
					CSApplicationData iApp = ApplicationSet.Applications[item.ApplicationType];

					if ((iApp != null) && (iApp.Enabled))
						list.AddRange(iApp.Sections());
				}

				sb.Append(" AND P.SectionID IN(");
				for(int i =0; i < list.Count; i++)
				{
					sb.Append(((Section) list[i]).SectionID.ToString());
					if ((i+1) < list.Count)
					{
						sb.Append(",");
					}
				}
				sb.Append("))");
            }

			if (hasSectionsFilter)
			{
				sb.Append(")");
			}
			else
			{
				ArrayList list = new ArrayList();
				foreach(BarrelItem item in items.Values)
				{
					CSApplicationData iApp = ApplicationSet.Applications[item.ApplicationType];

					if ((iApp != null) && (iApp.Enabled))
						list.AddRange(iApp.Sections());
				}

				sb.Append(" AND P.SectionID IN (");
				for(int i =0; i < list.Count; i++)
				{
					sb.Append(((Section) list[i]).SectionID.ToString());
					if ((i+1) < list.Count) 
					{
						sb.Append(",");
					} 
				}
				sb.Append(")");
			}


			// TAGs
			if (bar.Query.HasTags)
			{
				StringBuilder listOfTags = new StringBuilder();
				foreach (string tag in bar.Query.Tags)
				{
					if (listOfTags.Length > 0)
						listOfTags.Append(",");

					listOfTags.Append("N'");
					listOfTags.Append(tag.Replace("'", "''"));
					listOfTags.Append("'");
				}

				ArrayList list = new ArrayList();
				foreach(BarrelItem item in items.Values)
				{
					CSApplicationData iApp = ApplicationSet.Applications[item.ApplicationType];

					if ((iApp != null) && (iApp.Enabled))
						list.AddRange(iApp.Sections());
				}

				if (!bar.Query.LogicallyOrTags)
				{
					// and tags
					sb.Append(" AND P.PostID in (select tP.PostID from cs_Post_Categories tC inner join cs_Posts_InCategories tPiC on tPiC.CategoryID = tC.CategoryID inner join cs_Posts tP on tPiC.PostID = tP.PostID where tC.IsEnabled = 1 and tC.Name in (");
					sb.Append(listOfTags.ToString());
					sb.Append(") and tC.SettingsID = ");
					sb.Append(settingsID.ToString());
					sb.Append("	and tP.SettingsID = ");
					sb.Append(settingsID.ToString());

					sb.Append(" and tC.SectionID IN (");
					for(int i =0; i < list.Count; i++)
					{
						sb.Append(((Section) list[i]).SectionID.ToString());
						if ((i+1) < list.Count) 
						{
							sb.Append(",");
						} 
					}
					sb.Append(")");

					sb.Append(" group by tP.PostID, tP.PostDate having count(*) = ");
					sb.Append(bar.Query.Tags.Length);
					sb.Append(")" );
				}
				else
				{
					// or tags
					sb.Append(" AND P.PostID in (select tPiC.PostID from cs_Post_Categories tC inner join cs_Posts_InCategories tPiC on tPiC.CategoryID = tC.CategoryID where tC.Name in (");
					sb.Append(listOfTags.ToString());
					sb.Append(") and tC.SettingsID = ");
					sb.Append(settingsID.ToString());

					sb.Append(" and tC.SectionID IN (");
					for(int i =0; i < list.Count; i++)
					{
						sb.Append(((Section) list[i]).SectionID.ToString());
						if ((i+1) < list.Count) 
						{
							sb.Append(",");
						} 
					}
					sb.Append(")");

					sb.Append(" and tC.IsEnabled = 1 ) ");
				}
			}

            // Users
            //
			ArrayList userIDs;
			if (bar.Query.UsersToSearch != null)
				userIDs = new ArrayList(bar.Query.UsersToSearch);
			else
				userIDs = new ArrayList();

			if (bar.Query.UserNamesToSearch != null && bar.Query.UserNamesToSearch.Length > 0)
			{
				for (int i = 0; i < bar.Query.UserNamesToSearch.Length; i++) 
				{
					try
					{
						User user = CommunityServer.Users.FindUserByUsername(bar.Query.UserNamesToSearch[i].TrimStart(' ').TrimEnd(' '));
						userIDs.Add(user.UserID);
					}
					catch
					{
					}
				}
			}

            if (userIDs.Count > 0) 
            {
                string usersSearch = string.Empty;

                sb.Append(" AND P.UserID IN (");
                for (int i = 0; i < userIDs.Count; i++) 
                {
                    string barrel = "P.UserID = ";
                    sb.Append((int) userIDs[i]);

                    // Build the clauses
                    usersSearch += barrel + ((int) userIDs[i]).ToString();

                    if ((i+1) < userIDs.Count) 
                    {
                        sb.Append(",");
                    } 
                }
                sb.Append(")");
            }

            clauses[4] = sb.ToString();

            // Main Loop for AND clause in search terms
            //
            int loopCount = 0;
            
            if (null != bar.Terms.And)
                loopCount = bar.Terms.And.Length;

            if (hasORterms)
                loopCount ++;

            if (loopCount > 0) 
            {
                for (int i = 0; i < loopCount; i++) 
                {
                    string barrel = "B{0}";

                    clauses[0] += string.Format(barrel, i) + ".Weight";
                    clauses[1] += "cs_SearchBarrel " + string.Format(barrel, i) + ", ";
                    clauses[3] += string.Format(barrel, i) + ".PostID = P.PostID ";

                    if ((i+1) < loopCount) 
                    {
                        clauses[0] += " + ";
                        clauses[3] += " AND ";
                    }
                }
            } 
            else 
            {
                clauses[0] = "0";
                clauses[1] = "";
                clauses[3] = "1 = 1";
            }

            clauses[5] = settingsID.ToString();

            //9 DateFilter
            if(bar.Query.HasDate)
            {
                //Should be do this based on UserTime
                clauses[8] = string.Format(" AND (P.PostDate >= '{0}' AND P.PostDate <= '{1}') ", GetSafeSqlDateTimeFormat(bar.Query.StartDate), GetSafeSqlDateTimeFormat(bar.Query.EndDate.AddDays(1)));
            }

            switch(bar.Query.SortBy)
            {
 
                case SearchSort.Index:
                    clauses[9] = " B0.PostID ASC";
                    break;

                case SearchSort.DateAscending:
                    clauses[9] = " P.PostDate ASC";
                    break;
                case SearchSort.DateDescending: 
                    clauses[9] = " P.PostDate DESC";
                    break;
                case SearchSort.Relevance:
                default:
                    clauses[9] = " Weight DESC, PostDate DESC";
                    break;
            }


            return  string.Format(searchSQL, clauses);
        }

        private static string GetSafeSqlDateTimeFormat(DateTime date)
        {
            return date.ToString(System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.SortableDateTimePattern);
        }
        #endregion

	}
}
